﻿--use PolePosition
GO


IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_Login')
	DROP PROCEDURE up_Login

GO

CREATE PROCEDURE [dbo].[up_Login]
	 @UserName varchar(50)
	,@Password varchar(50)
AS


	DECLARE @ContactId int
	DECLARE @CompanyKey int
	DECLARE @UtilityFk int
	DECLARE @Type varchar(50)

	SELECT
		 @ContactId = ContactId
		,@CompanyKey = CompanyKey
		,@Type = Type
		,@UtilityFk = UtilityFk
	FROM
	(
		SELECT Vendor_ContactId AS ContactId, VendorFk AS CompanyKey, NULL AS UtilityFk, UserName, [Password], 'Vendor' AS Type FROM tb_Vendor_Contacts
		UNION
		SELECT Utility_ContactId AS ContactId, UtilityFk AS CompanyKey, UtilityFk, UserName, [Password], 'Utility' FROM tb_Utility_Contacts
		UNION
		SELECT NULL AS ContactId, NULL AS CompanyKey, NULL AS UtilityFk, 'PPI', 'Passw0rd', 'PPI' 
	) AS Contacts
	WHERE 
		UserName = @UserName
		AND [Password] = @Password COLLATE SQL_Latin1_General_CP1_CS_AS --ensure case sensitivity

	--first table simply returns type
	SELECT @Type as Type

	IF (@Type = 'Vendor')
	BEGIN
		--projects
		SELECT * FROM v_tb_Vendor_Projects WHERE VendorFk = @CompanyKey

		--vendor info
		SELECT * FROM v_tb_Vendors WHERE VendorId = @CompanyKey

		--vendor contact info
		SELECT * FROM v_tb_Vendor_Contacts WHERE Vendor_ContactId = @ContactId
		
		--utility info
		SELECT DISTINCT 
			v_tb_Utilities.* 
		FROM v_tb_Utilities 
		INNER JOIN tb_Utility_Cross_Vendor 
			ON UtilityId = UtilityFk
		WHERE VendorFk = @CompanyKey
		
		--utility contact info
		SELECT 
			 UtlityContact.*
			,UtlityContact.Name + ' (' + Utility.Name + ')' AS FriendlyName
		FROM tb_Utility_Contacts UtlityContact
		INNER JOIN tb_Utilities Utility
			ON UtilityId = UtlityContact.UtilityFk
		INNER JOIN tb_Utility_Cross_Vendor UtilityVendors
			ON UtilityVendors.UtilityFk = Utility.UtilityId
		WHERE VendorFk = @CompanyKey


		RETURN
	END

	IF (@Type = 'Utility')
	BEGIN
		--projects
		SELECT * FROM v_tb_Vendor_Projects WHERE UtilityFk = @CompanyKey

		--utility info
		SELECT * FROM v_tb_Utilities WHERE UtilityId = @CompanyKey

		--utility contact info
		SELECT * FROM v_tb_Utility_Contacts WHERE Utility_ContactId = @ContactId
		
		--all polygons for the utility
		SELECT * FROM v_tb_Utility_Polygons --WHERE UtilityFk = @UtilityFk OR @UtilityFk = null
		
		--vendor info
		SELECT Vendors.* 
		FROM v_tb_Vendors Vendors
		INNER JOIN tb_Utility_Cross_Vendor UtilityVendors
			ON UtilityVendors.VendorFk = Vendors.VendorId
		WHERE UtilityVendors.UtilityFk = @CompanyKey

		--vendor contact info
		SELECT 
			 Contacts.*
			,Contacts.Name + ' (' + Vendors.[Name] + ')' AS FriendlyName
		FROM tb_Vendor_Contacts Contacts
		INNER JOIN v_tb_Vendors Vendors 
			ON Vendors.VendorId = Contacts.VendorFk
		INNER JOIN tb_Utility_Cross_Vendor UtilityVendors 
			ON UtilityVendors.VendorFk = Vendors.VendorId
		WHERE UtilityVendors.UtilityFk = @CompanyKey
		

		RETURN
	END


	IF (@Type = 'PPI')
	BEGIN
		--only projects (all)
		SELECT * FROM v_tb_Vendor_Projects

		--all polygons
		SELECT * FROM v_tb_Utility_Polygons 

		--utility info
		SELECT DISTINCT 
			v_tb_Utilities.* 
		FROM v_tb_Utilities 

		--utility contact info
		SELECT 
			 UtlityContact.*
			,UtlityContact.Name + ' (' + Utility.Name + ')' AS FriendlyName
		FROM tb_Utility_Contacts UtlityContact
		INNER JOIN tb_Utilities Utility
			ON UtilityId = UtlityContact.UtilityFk

		--vendor info
		SELECT
			 Vendors.VendorId
			,Vendors.[Name]
			,UtilityVendors.UtilityFk
			,Vendors.Status
			,Vendors.FriendlyName
		FROM v_tb_Vendors Vendors
		INNER JOIN tb_Utility_Cross_Vendor UtilityVendors
			ON UtilityVendors.VendorFk = Vendors.VendorId

		--vendor contact info
		SELECT 			 
			 Contacts.*
			,Contacts.Name + ' (' + Vendors.Name + ')' AS FriendlyName
		FROM tb_Vendor_Contacts Contacts
		INNER JOIN v_tb_Vendors Vendors 
			ON Vendors.VendorId = Contacts.VendorFk

		RETURN
	END





GO


--set the property for the SP that it will generated
EXEC sys.sp_addextendedproperty @name=N'Generate', @value=N'True' , 
	@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'PROCEDURE',
	@level1name=N'up_Login'
GO

